package ks.server.persist;

import java.net.URL;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.UUID;

import ks.managers.icon.*;

/**
 * Provides persistent database access to managers.
 * 
 * @author George Heineman
 */
public class Database {
	/** Hard-coded database access information */
	/**
	 * Should you choose an alternate MYSQL database system, you will need to
	 * update these credentials. 
	 *
	 * This information must be kept reasonably private
	 */
	private static final String DB_SERVER   = "mysql.wpi.edu";
	private static final String DB_USER     = "emc_manager";
	private static final String DB_PASSWORD = "VAdQMH";
	private static final String DB_DATABASE = "emc_example";

	// as long as you're using mysql, leave this alone.
	private static final String DB_TYPE = "mysql";

	/** Max image size. */
	public static final long maxImageSize = 65536;
	
	/* ------------- SQL Variables ------------- */

	/** The SQL connection to the database */
	static Connection con;
	
	/**
	 * Gets a connection to for the manager and makes a good effort to
	 * make sure it is open
	 * @return either an open connection or null
	 */
	static synchronized Connection getConnection() {
		try {
			if (con != null && con.isClosed())
			{
				con = null;
			}
		} catch (SQLException e) {
			con = null;
		}
		connect();
		return con;
	}
	
	/** Closes the database connection */
	public static void disconnect () {
		if (con == null) {
			return;
		}
		
		try {
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		con = null;
	}

	/**
	 * To access the database, one must first connect.
	 * 
	 * @return false should the connection fails; true otherwise.
	 */
	public static boolean connect() {
		// already connected.
		if (con != null) { return true; }

		// if you want to access and test against a Microsoft Access DB use following
		// instead of the connectMYSQL.
		
		return connectMYSQL();
		//return connectPrivately();
	}
	
	/** 
	 * Utility method to validate connection is valid.
	 * @return true if DB is available; false otherwise.
	 */
	public static boolean isConnected() {
		if (con == null) { return false; }
		
		try {
			return !con.isClosed();
		} catch (SQLException e) {
			e.printStackTrace();
			
			return false;
		}
	}

	/**
	 * The database needs unique IDs so use this method to create them.
	 * <p>
	 * Note that the unique id is only statistically unique, but the odds are
	 * distinctly in our favor that there will not be duplicates in our lifetime.
	 * 
	 * @return
	 */
	public static String createUniqueID() {
		// create unique ID
		UUID uid = UUID.randomUUID();
		return uid.toString();
	}	
	/**
	 * To access private MS-DB access database
	 * 
	 * @return false should the connection fails; true otherwise.
	 */
	static boolean connectPrivately() {
		// already connected.
		if (con != null) { return true; }

		// Register the JDBC driver for MySQL. Simply accessing the class
		// will properly initialize everything.
		try {
			//Class.forName("com.mysql.jdbc.Driver");
			Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
		} catch (ClassNotFoundException cnfe) {
			System.err.println ("Make sure you Update your CompUnitEnvironment to retrieve required jdbc drivers...");
			return false;
		}

		//Define URL for database server 
		// NOTE: must fill in DATABASE NAME
		//String url =  "jdbc:" + DB_TYPE + "://" + DB_SERVER + "/" + DB_DATABASE;

		try {
			// Get a connection to the database for a
			// user with the given user name and password.
			// con = DriverManager.getConnection(url, DB_USER, DB_PASSWORD);
			con = DriverManager.getConnection(
				     "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=scratchDB.mdb"
				      );
			return true;
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		}
	}
	
	/**
	 * To access remote MYSQL database
	 * 
	 * @return false should the connection fails; true otherwise.
	 */
	static boolean connectMYSQL() {
		// already connected.
		if (con != null) { return true; }

		// Register the JDBC driver for MySQL. Simply accessing the class
		// will properly initialize everything.
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException cnfe) {
			System.err.println ("Make sure you Update your CompUnitEnvironment to retrieve required jdbc drivers...");
			return false;
		}

		//Define URL for database server 
		// NOTE: must fill in DATABASE NAME
		String url =  "jdbc:" + DB_TYPE + "://" + DB_SERVER + "/" + DB_DATABASE;

		try {
			// Get a connection to the database for a
			// user with the given user name and password.
			con = DriverManager.getConnection(url, DB_USER, DB_PASSWORD);
			return true;
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		}
	}

	// make it possible to retrieve all existing Icons (presumably once at startup)
	public static Collection<Icon> getIcons() throws Exception {
		Connection con = getConnection();
		ArrayList<Icon> icons = new ArrayList<Icon>();
		
		try {
			PreparedStatement pstmt = con.prepareStatement("SELECT * FROM icons;");
			
			// Create the statement handler
			// Execute the SQL statement and store result into the ResultSet
			ResultSet result = pstmt.executeQuery();
			
			while (result.next()) {
				String name = result.getString("name");
				String url = result.getString("url");

				Icon icon = new Icon(name, new URL(url));
				icons.add(icon);
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
			throw new IllegalArgumentException ("Unable to get icons!");
		}
		
		return icons;
	}
	
	// make it possible to add a new icon
	public static boolean addIcon (String name, String urlLocation) throws Exception {
		Connection con = getConnection();
		
		try {
			PreparedStatement pstmt = con.prepareStatement(
				"INSERT into icons(name,url) VALUES(?,?);");
			pstmt.setString(1, name);
			pstmt.setString(2, urlLocation);
			
			// Create the statement handler
			// Execute the SQL statement and store result into the ResultSet
			pstmt.executeUpdate();
			
			int numInserted = pstmt.getUpdateCount();
			if (numInserted == 0) {
				throw new IllegalArgumentException ("Unable to insert " + name + ".");
			}
		} catch (SQLException e) {
			throw new IllegalArgumentException (e.getMessage());
		}
		
		return true;
	}
	
}
